package ca.cbc.security;

import java.util.*;
import java.io.*;
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.Connection;
import java.sql.SQLException;

import org.apache.commons.lang.StringEscapeUtils;
import org.apache.log4j.Logger;

import ca.cbc.util.*;

public class AuthDBData {

  private static Logger log = Logger.getLogger(AuthDBData.class);
  protected StackTraceToString stts = new StackTraceToString();

  protected Connection connection;

  public AuthDBData() {}

  protected boolean userExists(String username) throws SQLException {
	
	boolean exists = false;
	
	username = StringEscapeUtils.escapeSql(username);
	
	String selectedField = "user_name";
	
	Map<String,String> select = new HashMap<String,String>();
	
	select.put(selectedField, "");

	List<Map<String,String>> rows = executeQuery("SELECT "+selectedField+" FROM users WHERE "+selectedField+" = '" + username + "'",select);

	for(Map<String,String> fields : rows) {
	
		if(fields.get(selectedField).equals(username)) {
			exists = true;
		}
	
	}

	return exists;

  }

  protected String getUserPassword(String username) throws SQLException {

	if(userExists(username)) {
		
		username = StringEscapeUtils.escapeSql(username);
		
		String selectedField = "user_pass";
		
		Map<String,String> select = new HashMap<String,String>();
		
		select.put(selectedField, "");

		List<Map<String,String>> rows = executeQuery("SELECT "+selectedField+" FROM users WHERE user_name = '" + username + "'",select);

		String user_pass = "";
		
		for(Map<String,String> fields : rows) {
			user_pass = fields.get(selectedField);
		}
		
		return user_pass;
	} else {
		String ns = "";
		return ns;
	}

  }

  public ArrayList<String> getUserRoles(String username) throws SQLException {

	ArrayList<String> list = new ArrayList<String>();

	if(userExists(username)) {
		
		username = StringEscapeUtils.escapeSql(username);
		
		Map<String,String> select = new HashMap<String,String>();
		
		String appName = "app_name";
		String roleName = "role_name";
		
		select.put(appName, "");
		select.put(roleName, "");
		
		String joinQuery = "SELECT * FROM roles_for_users_list WHERE user_name = '"+username+"'";

		List<Map<String,String>> rows = executeQuery(joinQuery,select);

		for(Map<String,String> fields : rows) {
			
			String role = fields.get(appName)+":"+fields.get(roleName);
			
			list.add(role);
		}

	}

	return list;

  }

  public boolean authenticate(String un, String pw) throws SQLException {
	
	Digest dpw = new Digest(pw);

	if(userExists(un) && dpw.getHash().compareTo(getUserPassword(un)) == 0) {
		return true;
	} else {
		return false;
	}

  }

  protected List<Map<String,String>> executeQuery(String query, Map<String,String> select) throws SQLException {

	if(connection == null) {
		connection = new AuthDBConnect().getConnection();
	}
		
	Statement s = null;
	ResultSet rs = null;

	s = connection.createStatement();
	rs = s.executeQuery(query);
	
	Set<String> fieldNames = select.keySet();
	
	List<Map<String,String>> rows = new ArrayList<Map<String,String>>();
	
	while(rs.next()) {
		Map<String,String> fields = new HashMap<String, String>();
		for(String field : fieldNames) {
			fields.put(field, rs.getString(field));
		}
		rows.add(fields);
	}
	
	rs.close();
	s.close();
	connection.close();
	connection = null;

	return rows;

  }
  
  protected int executeUpdate(String query) throws SQLException {

	if(connection == null) {
		connection = new AuthDBConnect().getConnection();
	}
		
	Statement s = null;
	int rowsAffected = 0;

	s = connection.createStatement();
	rowsAffected = s.executeUpdate(query);
	s.close();
	connection.close();
	connection = null;

	return rowsAffected;

  }

}
